package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;
import static cam.School.DataAccessObject.DAOUtil.toSqlDate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.InfrastructurDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.EmployeeDTO;
import cam.School.DataTransfertObject.InfrastructurDTO;
import cam.School.DataTransfertObject.MaterialDTO;
import cam.School.DataTransfertObject.StudentDTO;


public class InfrastructurDAOImpl implements InfrastructurDAO {
	
	private static final String QUERY_PARAMS = "id, infrastructurCategoryId, name,"
			+ " description, numberOfSits, firstRegistration, lastUpdate, personInCharge,"
			+ " state";
	
	private static final String QUERY_MATERIAL_PARAMS = "id, categoryId, name,"
			+ " description, inUse, firstRegistration, lastUpdate, personInCharge,"
			+ " state, serialnumber";
	
	private static final String UPDATE_INFRASTRUCTURE_QUERY_PARAMS = "infrastructureCategoryId = ?,"
			+ " name = ?, description = ?, "
			+ " numberOfSits = ?, area = ?,"
			+ " lastUpdate = ?, personInCharge = ?,state = ?";
	
	private static final String UPDATE_MATERIAL_QUERY_PARAMS = "name = ?, description = ?, "
			+ " lastUpdate = ?, categoryId = ?, state = ?, serialNumber = ?, personInCharge = ?";
	
	private static final String SQL_FIND_ALL = "SELECT "
			+ QUERY_PARAMS
			+ " FROM infrastructur WHERE isDeleted = 0";
	
	private static final String SQL_FIND_ALL_MATERIAL = "SELECT "
			+ QUERY_MATERIAL_PARAMS
			+ " FROM material WHERE isDeleted = 0";
	
	private static final String SQL_FIND_IMAGE_BY_INFRASTRUCTUR_ID = "SELECT location " 
			+ " FROM Image WHERE  objectId = ?";

	private static final String SQL_DELETE_BY_ID = " UPDATE infrastructur SET isDeleted = 1 WHERE id = ?";
	
	private static final String SQL_DELETE_MATERIAL_BY_ID = " UPDATE material SET isDeleted = 1 WHERE id = ?";
	
	private static final String SQL_UPDATE_INFRASTRUCTUR_BY_ID = "UPDATE infrastructure SET "
			+ UPDATE_INFRASTRUCTURE_QUERY_PARAMS
			+ " WHERE id = ? AND isDeleted = 0";
	
	private static final String SQL_UPDATE_MATERIAL_BY_ID = "UPDATE material SET "
			+ UPDATE_MATERIAL_QUERY_PARAMS
			+ " WHERE id = ? AND isDeleted = 0";
	
	private static final String SQL_FIND_INFRASTRUCTUR_BY_CATEGORYNAME = "SELECT "
			+  QUERY_PARAMS
			+" FROM infrastructur"
			+" where infrastructur.infrastructurCategoryId = ? AND infrastructur.isDeleted = 0";
	
	private static final String SQL_FIND_MATERIAL_BY_CATEGORYNAME = "SELECT "
			+  QUERY_PARAMS
			+" FROM material"
			+" where material.categoryId = ? AND material.isDeleted = 0";
	// Vars
		
	// ---------------------------------------------------------------------------------------
	
	private DAOFactory daoFactory;
	
	public InfrastructurDAOImpl(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	@Override
	public List<InfrastructurDTO> getAllInfrastructur() throws SQLException {
		Object[] values = {};
		return getAllInfrastructure(SQL_FIND_ALL, values);
	}
	
	public List<InfrastructurDTO> getAllInfrastructure(String sqlQuery, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<InfrastructurDTO> infrastructures = new ArrayList<InfrastructurDTO>();

		try {
			connection = daoFactory.getConnection();
			if(values.length == 0 ){
				preparedStatement = connection.prepareStatement(sqlQuery);
			}else{
				preparedStatement = prepareStatement(connection, sqlQuery, false, values);
			}
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				infrastructures.add(mapInfrastructur(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return infrastructures;
	}	
	
	public List<MaterialDTO> getAllMaterial(String sqlQuery, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<MaterialDTO> materials = new ArrayList<MaterialDTO>();

		try {
			connection = daoFactory.getConnection();
			if(values.length == 0 ){
				preparedStatement = connection.prepareStatement(sqlQuery);
			}else{
				preparedStatement = prepareStatement(connection, sqlQuery, false, values);
			}
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				materials.add(mapMaterial(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return materials;
	}
	private InfrastructurDTO mapInfrastructur(ResultSet resultSet)
			throws SQLException {
		InfrastructurDTO infrastructure = new InfrastructurDTO();
		infrastructure.setId(resultSet.getLong("id"));
		infrastructure.setCategoryId(resultSet.getLong("infrastructurCategoryId"));
		infrastructure.setName(resultSet.getString("name"));
		infrastructure.setDescription(resultSet.getString("description"));
		infrastructure.setNumberOfSits(resultSet.getInt("numberOfSits"));
		infrastructure.setFirstRegistration(resultSet.getDate("firstRegistration"));
		infrastructure.setLastUpdate(resultSet.getDate("lastUpdate"));
		infrastructure.setPersonInCharge(resultSet.getString("personInCharge"));
		infrastructure.setState(resultSet.getString("state"));
		infrastructure.setPictureLocationPath(getInfrastructurImagePathLocation(infrastructure.getId()));
		return infrastructure;
	}
	
	
	private MaterialDTO mapMaterial(ResultSet resultSet)
			throws SQLException {
		MaterialDTO material = new MaterialDTO();
		material.setId(resultSet.getLong("id"));
		material.setCategoryId(resultSet.getLong("categoryId"));
		material.setName(resultSet.getString("name"));
		material.setDescription(resultSet.getString("description"));
		material.setInUse(resultSet.getBoolean("inUse"));
		material.setFirstRegistration(resultSet.getDate("firstRegistration"));
		material.setLastUpdate(resultSet.getDate("lastUpdate"));
		material.setPersonInCharge(resultSet.getString("personInCharge"));
		material.setState(resultSet.getString("state"));		
		material.setSerialNumber(resultSet.getString("serialNumber"));
		material.setPictureLocationPath(getInfrastructurImagePathLocation(material.getId()));
		return material;
	}
	
	private List<String> getInfrastructurImagePathLocation(long infrastructurId){
		return getInfrastructurImagePathLocation(SQL_FIND_IMAGE_BY_INFRASTRUCTUR_ID, infrastructurId);		
	}
	
	private List<String> getInfrastructurImagePathLocation(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		List<String> imagePathCollection = new ArrayList<String>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				imagePathCollection.add(resultSet.getString("location"));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return imagePathCollection; //TO DO 
	}
	
	@Override
	public CallResult createInfrastructur(InfrastructurDTO infrastructur) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		long result;
		try {
			connection = daoFactory.getConnection();
			
			callableStatement = connection.prepareCall("{call createInfrastructurProcedure(?,?,?,?,?,?,?,?,?,?)}");			
			
			callableStatement.registerOutParameter(1, java.sql.Types.BIGINT);
			callableStatement.setLong(2, infrastructur.getCategoryId());
			callableStatement.setString(3, infrastructur.getName());
			callableStatement.setString(4, infrastructur.getDescription());
			callableStatement.setInt(5, infrastructur.getNumberOfSits());
			callableStatement.setBoolean(6, false);
			callableStatement.setDate(7, toSqlDate(infrastructur.getFirstRegistration()));
			callableStatement.setDate(8, toSqlDate(infrastructur.getLastUpdate()));						
			callableStatement.setString(9, infrastructur.getPersonInCharge());			
			callableStatement.setString(10, infrastructur.getState());	
			
			callableStatement.executeUpdate();			
			result = callableStatement.getLong(1);
			
			if(new Integer((int)result).equals(new Integer(0))){
				System.out.println("not duplicated allowed " + result);
				return new CallResult(406,"not duplicated allowed");
			}
			
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.toString());
			return new CallResult(406,e.toString());
		} finally {
			close(connection, callableStatement);
		}
		return new CallResult(result,"success") ;
		
	}

	@Override
	public CallResult updateInfrastructur(InfrastructurDTO infrastructur) {
		Object[] values = { 
				infrastructur.getCategoryId(),
				infrastructur.getName(), 
				infrastructur.getDescription(),
				infrastructur.getNumberOfSits(), 
				toSqlDate(infrastructur.getLastUpdate()),
				infrastructur.getPersonInCharge(), 				
				infrastructur.getState(), 
				infrastructur.getId() 
		};

		try (Connection connection = daoFactory.getConnection();
				PreparedStatement statement = prepareStatement(connection,
						SQL_UPDATE_INFRASTRUCTUR_BY_ID, false, values);) {
			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				return new CallResult(406,
						"Updating infrastructure failed, no rows affected.");
			}
		} catch (SQLException e) {
			return new CallResult(406, e.toString());
		}
		return new CallResult(200, "success");		
	}

	@Override
	public CallResult deleteInfrastructur(long infrastructurId) throws Exception {
		Connection connection = null;
		PreparedStatement deleteByIdPreparedStatement = null;
				
		try {
			connection = daoFactory.getConnection();
			connection.setAutoCommit(false);
			deleteByIdPreparedStatement = connection.prepareStatement(SQL_DELETE_BY_ID);
			deleteByIdPreparedStatement.setLong(1, infrastructurId);
			deleteByIdPreparedStatement.executeUpdate();
			
			connection.commit();			
		} catch (SQLException e) {
			connection.rollback();
			new CallResult(405, e.toString());
		} finally {
			close(connection, deleteByIdPreparedStatement);
		}
		return new CallResult(200, "sucess");		
	}

	

	@Override
	public InfrastructurDTO getInfrastructurById(int infrastructurId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<MaterialDTO> getAllMaterial() throws SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<MaterialDTO> materials = new ArrayList<MaterialDTO>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(SQL_FIND_ALL_MATERIAL);
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				materials.add(mapMaterial(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return materials;
	}

	@Override
	public CallResult createMaterial(MaterialDTO material) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		long result;
		try {
			connection = daoFactory.getConnection();
			
			callableStatement = connection.prepareCall("{call createMaterialProcedure(?,?,?,?,?,?,?,?,?,?,?)}");			
			
			callableStatement.registerOutParameter(1, java.sql.Types.BIGINT);
			callableStatement.setString(2, material.getName());
			callableStatement.setString(3, material.getDescription());
			callableStatement.setBoolean(4, false);
			callableStatement.setBoolean(5, false);
			callableStatement.setDate(6, toSqlDate(new Date()));
			callableStatement.setDate(7, toSqlDate(new Date()));						
			callableStatement.setLong(8, material.getCategoryId());			
			callableStatement.setString(9, material.getState());	
			callableStatement.setString(10, material.getSerialNumber());
			callableStatement.setString(11, material.getPersonInCharge());
			callableStatement.executeUpdate();			
			result = callableStatement.getLong(1);
			
			if(new Integer((int)result).equals(new Integer(0))){
				return new CallResult(406,"not duplicated allowed");
			}
			
		} catch (SQLException e) {
			return new CallResult(406,e.toString());
		} finally {
			close(connection, callableStatement);
		}
		return new CallResult(result,"success") ;
	}

	@Override
	public CallResult updateMaterial(MaterialDTO material) {
		Object[] values = { 
				material.getName(), 
				material.getDescription(),
				toSqlDate(material.getLastUpdate()),
				material.getCategoryId(),
				material.getState(),				
				material.getSerialNumber(), 
				material.getPersonInCharge(), 								 
				material.getId() };

		try (Connection connection = daoFactory.getConnection();
				PreparedStatement statement = prepareStatement(connection,
						SQL_UPDATE_MATERIAL_BY_ID, false, values);) {
			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				return new CallResult(406,
						"Updating material failed, no rows affected.");
			}
		} catch (SQLException e) {
			return new CallResult(406, e.toString());
		}
		return new CallResult(200, "success");
		
	}

	@Override
	public CallResult deleteMaterial(long materialId) throws Exception {
		Connection connection = null;
		PreparedStatement deleteByIdPreparedStatement = null;
				
		try {
			connection = daoFactory.getConnection();
			deleteByIdPreparedStatement = connection.prepareStatement(SQL_DELETE_MATERIAL_BY_ID);
			deleteByIdPreparedStatement.setLong(1, materialId);
			deleteByIdPreparedStatement.executeUpdate();
			
			connection.commit();			
		} catch (SQLException e) {
			connection.rollback();
			new CallResult(405, e.toString());
		} finally {
			close(connection, deleteByIdPreparedStatement);
		}
		return new CallResult(200,"sucess");
	}

	@Override
	public List<InfrastructurDTO> getByInfrastructurByCategory(long infrastructurCategoryId) {
		Object[] values = {infrastructurCategoryId};
		return getAllInfrastructure(SQL_FIND_INFRASTRUCTUR_BY_CATEGORYNAME, values);	
	}
	
	
	
	@Override
	public List<MaterialDTO> getByMaterialByCategory(long materialCategoryId) {
		Object[] values = {materialCategoryId};
		return getAllMaterial(SQL_FIND_MATERIAL_BY_CATEGORYNAME, values);	
	}
	
	@Override
	public MaterialDTO getMaterialById(int materialId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean insertTimetable(int infrastructurId, String date,
			String thema, String organisator, String from, String to) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public InfrastructurDTO getTimetableById(int infrastructurId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public InfrastructurDTO[] getAllTimetable() {
		// TODO Auto-generated method stub
		return null;
	}
}
